﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessObjectLayer
{
    /// <summary>
    /// Class DBStation.
    /// </summary>
    public class DBStation : DBClass, IDBStation
    {
        /// <summary>
        /// Gets all stations from the database.
        /// </summary>
        /// <returns>List{Station}.</returns>
        public List<Station> GetAllStations()
        {
            List<Station> list = new List<Station>();
            foreach (var o in MiscWhere(""))
            {
                list.Add((Station)o);
            }
            return list;
        }

        /// <summary>
        /// Gets a station from the database
        /// </summary>
        /// <param name="id">The identifier.</param>
        /// <returns>Station.</returns>
        public Station GetStation(long id)
        {
            String wClause = "Id = '" + id + "'";
            return (Station)SingleWhere(wClause);
        }

        /// <summary>
        /// Inserts a station in the database.
        /// </summary>
        /// <param name="station">The station.</param>
        /// <returns>The number of affected rows</returns>
        public int InsertStation(Station station)
        {
            int nextId = GetMax.GetMaxId("SELECT MAX(id) FROM Station");
            nextId = nextId + 1;
            int rc = -1;

            const string query = "INSERT INTO Station(Latitude, Longtitude, Address, FK_Postalcode, Id, Name, Batteries) " +
                                 "VALUES(@Latitude, @Longtitude, @Address, @Postalcode, @Id, @Name, @Batteries)";

            var dbCon = new DBCon();
            using (SqlConnection con = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdIns = SqlCommand(con, out transaction);

                try
                {
                    cmdIns.CommandText = query;

                    using (cmdIns)
                    {
                        cmdIns.Parameters.Add(new SqlParameter("@Latitude", SqlDbType.Float) { Value = station.Latitude });
                        cmdIns.Parameters.Add(new SqlParameter("@Longtitude", SqlDbType.Float) { Value = station.Longtitude });
                        cmdIns.Parameters.Add(new SqlParameter("@Address", SqlDbType.VarChar) { Value = station.Address });
                        cmdIns.Parameters.Add(new SqlParameter("@Postalcode", SqlDbType.Int) { Value = station.PostalCode });
                        cmdIns.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = nextId });
                        cmdIns.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar) { Value = station.Name });
                        cmdIns.Parameters.Add(new SqlParameter("@Batteries", SqlDbType.Int) { Value = station.Batteries });
                        rc = cmdIns.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Insert in DBStation");
                }
            }
            if (rc == -1)
                throw new Exception("The station was not inserted");
            return rc;
        }

        /// <summary>
        /// Updates a station in the database
        /// </summary>
        /// <param name="station">The station.</param>
        /// <returns>The number of affected rows</returns>
        public int UpdateStation(Station station)
        {
            int rc = -1;
            const string query = "UPDATE Station SET " +
                                 "Batteries = @Batteries, " +
                                 "Name = @Name, " +
                                 "Address = @Address, " +
                                 "FK_PostalCode = @PostalCode," +
                                 "Longtitude = @Longtitude, " +
                                 "Latitude = @Latitude" +
                                 " WHERE Id = @Id";

            var dbCon = new DBCon();
            using (SqlConnection con = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                SqlCommand cmdUpd = SqlCommand(con, out transaction);
                try
                {
                    cmdUpd.CommandText = query;

                    using (cmdUpd)
                    {
                        cmdUpd.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = station.Id });
                        cmdUpd.Parameters.Add(new SqlParameter("@Batteries", SqlDbType.Int) { Value = station.Batteries });
                        cmdUpd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar) { Value = station.Name });
                        cmdUpd.Parameters.Add(new SqlParameter("@Address", SqlDbType.VarChar) { Value = station.Address });
                        cmdUpd.Parameters.Add(new SqlParameter("@PostalCode", SqlDbType.Int) { Value = station.PostalCode });
                        cmdUpd.Parameters.Add(new SqlParameter("@Longtitude", SqlDbType.Float) { Value = station.Longtitude });
                        cmdUpd.Parameters.Add(new SqlParameter("@Latitude", SqlDbType.Float) { Value = station.Latitude });
                        rc = cmdUpd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Update in DBStation");
                }
            }
            if (rc == -1)
                throw new Exception("The station was not updated");

            return rc;
        }

        /// <summary>
        /// Removes a station from the database.
        /// </summary>
        /// <param name="stationId">The station id.</param>
        /// <returns>The number of affected rows</returns>
        public int RemoveStation(long stationId)
        {
            int rc = -1;
            String query =
                "DELETE FROM Station" +
                " WHERE id = '" + stationId + "'";

            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdDel = SqlCommand(connection, out transaction);

                try
                {
                    cmdDel.CommandText = query;

                    using (cmdDel)
                    {
                        cmdDel.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = stationId });
                        rc = cmdDel.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

                catch (Exception e)
                {
                    TryRollback(e, transaction, "Delete in DBStation");
                }
                if (rc == -1)
                    throw new Exception("The station was not removed");

                return rc;
            }
        }

        /// <summary>
        /// Removes a battery from a list of stations in the database.
        /// </summary>
        /// <param name="stationList">The station list.</param>
        /// <returns><c>true</c> if the batteries were subtracted, <c>false</c> otherwise.</returns>
        /// <exception cref="System.Exception">No Batteries on station + tmpStation.Name</exception>
        public bool RemoveBatteries(List<Station> stationList)
        {
            bool isReserved = true;

            var dbCon = new DBCon();
            using (SqlConnection con = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                SqlCommand cmdUpd = SqlCommand(con, out transaction);
                try
                {
                    foreach (var station in stationList)
                    {
                        const string query = "UPDATE Station SET " +
                                             "Batteries = @Batteries, " +
                                             " WHERE Id = @Id;";
                        var tmpStation = GetStation(station.Id);
                        if (tmpStation.Batteries > 0)
                        {
                            tmpStation.Batteries -= 1;
                            using (cmdUpd)
                            {
                                cmdUpd.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = station.Id });
                                cmdUpd.Parameters.Add(new SqlParameter("@Batteries", SqlDbType.Int)
                                {
                                    Value = station.Batteries
                                });
                            }
                        }
                        else
                        {
                            throw new Exception("No Batteries on station " + tmpStation.Name);
                        }
                        cmdUpd.CommandText += query;
                        cmdUpd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    isReserved = false;
                    TryRollback(e, transaction, "Reservation failed: " + e.Message);
                }
            }
            return isReserved;
        }

        /// <summary>
        /// Builds a query with a 'where'-clause.
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        protected override string BuildQuery(string wClause)
        {
            String query = "select Station .*, City.City " +
                            "from Station JOIN City " +
                            "ON Station.FK_PostalCode=City.PostalCode";
            if (wClause.Length > 0)
            {
                query += " WHERE " + wClause;
            }
            return query;
        }

        /// <summary>
        /// Builds an object from either a dataset or a datareader The bool 'connected' defines if
        /// it is one or the other
        /// </summary>
        /// <param name="dataSetOrDataReader"></param>
        /// <param name="connected"></param>
        protected override object BuildObject(object dataSetOrDataReader, bool connected)
        {
            Station s = null;

            if (connected)
            {
                var dataReader = dataSetOrDataReader as SqlDataReader;

                if (dataReader != null)
                {
                    var address = (string)dataReader["address"];
                    var batteries = (int)dataReader["batteries"];
                    var city = (string)dataReader["city"];
                    var id = (long)dataReader["Id"];
                    var latitude = (double)dataReader["latitude"];
                    var longtitude = (double)dataReader["longtitude"];
                    var name = (string)dataReader["name"];
                    var postalCode = (int)dataReader["fk_postalCode"];

                    s = new Station(latitude, longtitude, address, postalCode, city, id, name, batteries);
                }
            }
            else
            {
                var dataRow = dataSetOrDataReader as DataRow;
                if (dataRow != null)
                    s = new Station
                    {
                        Address = (string)dataRow["address"],
                        Batteries = (int)dataRow["batteries"],
                        City = (string)dataRow["city"],
                        Id = (long)dataRow["Id"],
                        Latitude = (double)dataRow["latitude"],
                        Longtitude = (double)dataRow["longtitude"],
                        Name = (string)dataRow["name"],
                        PostalCode = (int)dataRow["fk_postalCode"]
                    };
            }
            object o = s;
            return o;
        }
    }
}